from sklearn.metrics import mean_squared_error
from math import sqrt
import pandas as pd
import numpy as np

if __name__ == '__main__':
    project_path = 'F:\\devops+\\投稿代码\\仿真模型\\data\\'
    repo_list = ["tensorflow", "netbeans", "phoenix", "Katello", "kuma", "moby", "opencv", "react", "scikit-learn",
                 "terraform"]  #
    map_result = {}
    for repo in repo_list:
        map_result[repo] = {}
        file_path = project_path + repo + '\\' + repo + '_real_simulation_accumulated.xlsx'
        df = pd.read_excel(file_path)
        df = df[df['real_open'] != 0]
        df = df[['date', 'real_open', 'simulation_open']]
        real_values = df['real_open'].to_numpy()
        predicted_values = df['simulation_open'].to_numpy()
        df['diff'] = df['simulation_open'] - df['real_open']
        MSPE = np.mean(np.square((predicted_values - real_values) / real_values))
        MMRE = np.mean(np.abs((predicted_values - real_values) / real_values))
        # df['MSPE'] = round(MSPE, 3)
        df['MMRE'] = round(MMRE, 3)
        map_result[repo]['MSPE'] = round(MSPE, 3)
        map_result[repo]['MMRE'] = round(MMRE, 3)
        df.columns = df.columns.map(lambda x: 'date' if x=='Unnamed: 0' else x)

        # 保留第五列第一行数据，其余设为空
        df.iloc[1:, 4] = None
        print(df)
        df.to_excel(project_path + repo + '\\' + repo + '_real_simulation_accumulated.xlsx', index=False)

    data = pd.DataFrame.from_dict(map_result)
    df = pd.DataFrame(data.values.T, columns=data.index, index=data.columns)
    df.index.name = "repo"
    print(df)
    # df.to_excel('mspe_mmre.xlsx', index=True)